package br.fatec.servico;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySQLResponsavelDAO implements IResponsavelDAO {

	private static final String cadastraResponsavel = "INSERT INTO responsavel "
			+ "(nome, setor, email, telefone, cnpj) VALUES (?, ?, ?, ?, ?) ";

	private static final String excluiResponsavel = "DELETE FROM responsavel WHERE "
			+ "cnpj = ? ";

	private static final String consultaResponsavel = "SELECT *  FROM responsavel WHERE "
			+ "cnpj = ? ";

	private static final String consultaNome = "SELECT *  FROM responsavel WHERE "
			+ "nome LIKE ? ";
	
	private static final String consultaNomePorEmpresa = "SELECT * FROM responsavel WHERE "
			+ "nome LIKE ? and cnpj = ? ";
	
	private static final String consultaCod = "SELECT * FROM responsavel WHERE "
			+ "codResponsavel = ? ";

	private static final String alteraResponsavel = "UPDATE responsavel SET nome = ? "
			+ ", setor = ?,  email = ?, telefone = ?, cnpj = ?, WHERE codResponsavel = ? ";

	String msg = "";

	@Override
	public int cadastra(String nomeResp, String setorResp, String emailResp,
			String telefoneResp, String cnpj) {
		PreparedStatement stmt;
		int rc = 0;
		try {
			stmt = MySQLDAOFactory.criaConexao().prepareStatement(
					cadastraResponsavel);
			stmt.setString(1, nomeResp);
			stmt.setString(2, setorResp);
			stmt.setString(3, emailResp);
			stmt.setString(4, telefoneResp);
			stmt.setString(5, cnpj);
			rc = stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			msg = e.getMessage();
			System.out
					.println("metodo cadastra responsavel =" + e.getMessage());
		}
		return rc;
	}

	@Override
	public int altera(String nomeResp, String setorResp, String emailResp,
			String telefoneResp, String cnpj) {
		PreparedStatement stmt;
		int rc = 0;
		try {
			stmt = MySQLDAOFactory.criaConexao().prepareStatement(
					alteraResponsavel);
			stmt.setString(1, nomeResp);
			stmt.setString(2, setorResp);
			stmt.setString(3, emailResp);
			stmt.setString(4, telefoneResp);
			stmt.setString(5, cnpj);
			rc = stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			msg = e.getMessage();
		}
		return rc;
	}

	@Override
	public ResultSet consulta(String cnpj) {
		ResultSet rs = null;
		PreparedStatement stmt;
		try {
			stmt = MySQLDAOFactory.criaConexao().prepareStatement(
					consultaResponsavel);
			stmt.setString(1, cnpj);
			rs = stmt.executeQuery();
			// stmt.close();
		} catch (SQLException e) {
			msg = e.getMessage();
			if (msg != null) {
				System.out.println(msg);
			}
		}
		return rs;
	}

	@Override
	public ResultSet consultaNome(String nome) {
		ResultSet rs = null;
		PreparedStatement stmt;
		try {
			stmt = MySQLDAOFactory.criaConexao().prepareStatement(consultaNome);
			stmt.setString(1, nome);
			rs = stmt.executeQuery();
			// stmt.close();
		} catch (SQLException e) {
			msg = e.getMessage();
		}
		return rs;
	}

	@Override
	public int exclui(int codResponsavel) {
		PreparedStatement stmt;
		int rc = 0;
		try {
			stmt = MySQLDAOFactory.criaConexao().prepareStatement(
					excluiResponsavel);
			stmt.setInt(1, codResponsavel);
			rc = stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			msg = e.getMessage();
		}
		return rc;
	}

	public String getMessage() {
		return msg;
	}

	@Override
	public ResultSet consultaCod(int cod) {
		ResultSet rs = null;
		PreparedStatement stmt;
		try {
			stmt = MySQLDAOFactory.criaConexao().prepareStatement(consultaCod);
			stmt.setInt(1, cod);
			rs = stmt.executeQuery();
			// stmt.close();
		} catch (SQLException e) {
			msg = e.getMessage();
		}
		return rs;
	}

}
